package com.jiedeng.dao;

import com.jiedeng.model.CorpPost;
import com.jiedeng.model.ProjectCorp;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author 覃远龙
 * @ClassName: ICorpPostRepository
 * @Description:
 * @date 2017/7/4 9:54
 */
public interface ICorpPostRepository extends JpaRepository<CorpPost, Long> {

    List<CorpPost> findByCorpId(long corpId);

    List<CorpPost> findByIdBetween(long min, long max);

    @Query(nativeQuery = true, value = " SELECT tcp.* FROM t_corp_post tcp " +
            "INNER JOIN ( " +
            " SELECT DISTINCT  " +
            "   case Corp_Prop_ID when 2 then 92 when 4 then 94 when 5 then 95 else null end Corp_Prop_ID " +
            " FROM t_project_corp WHERE " +
            "  Corp_ID IN (?1) AND Corp_Prop_ID IN (2, 4, 5) " +
            " ) a ON instr( tcp.Post_ID , a.Corp_Prop_ID )  ")
    List<CorpPost> findCorpPostByCorpIdsSQL(List<Long> corpIds);

    @Query(nativeQuery = true, value = " SELECT tcp.* FROM " +
            " t_corp_post tcp " +
            " INNER JOIN ( " +
            " SELECT " +
            "  tpc.Corp_Prop_ID, " +
            "  CASE tpc.Corp_Prop_ID " +
            " WHEN 2 THEN 92 " +
            " WHEN 4 THEN 94 " +
            " WHEN 5 THEN 95 " +
            " ELSE  NULL END x " +
            " FROM " +
            "  t_project_corp tpc " +
            " WHERE " +
            "  tpc.Corp_ID = ?1 " +
            " AND tpc.Project_ID = ?2 " +
            " AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
            " ) a ON instr(tcp.Post_ID, a.x) = 1 " +
            " ORDER BY  tcp.Post_ID ")
    List<CorpPost> findCorpPostByCorpIdSQL(Long corpId, Long projectId);
}
